package cc.catface.jpa;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface UserDao extends JpaRepository<User, Integer>/*, JpaSpecificationExecutor<User>*/ {

    /*Repository接口测试*/
    List<User> findByUsername(String username);

    List<User> findByUsernameLike(String username);

    List<User> findByUsernameLikeAndUserageGreaterThanEqual(String username, Integer userage);


    /*@Query接口测试*/
    @Query(value = "from User where username=?1")
    List<User> queryUserByUsername(String username);

    @Query(value = "from User where username like ?1")
    List<User> queryUserByUsernameLike(String username);

    //@Query(value = "from User where username like ?1 and userage >= ?2")//有and时这个写法变成username=?1
    @Query(value = "from User where username like concat('%',?1,'%') and userage >= ?2")
    List<User> queryUserByUsernameLikeAndUserage(String username, Integer userage);


    /*sql查询*/
    //nativeQuery默认是false表示不开启sql查询
    //@Query(value = "select * from t_user where username like ?1 and userage >= ?2", nativeQuery = true)//有and时这个写法变成username=?1
    @Query(value = "select * from t_user where username like concat('%',?1,'%') and userage >= ?2", nativeQuery = true)
    List<User> selectUserByUsernameLikeAndUserage(String username, Integer userage);


    /*@Query更新数据*/
    @Query("update User set userage=?2 where userid=?1")
    //表明当前是更新语句
    @Modifying
    //不加会报TransactionRequiredException
    @Transactional
    void updateUserageById(Integer id, Integer userage);
}
